package jdbc.persistence;

import jdbc.domain.Account;

import java.sql.*;

public class AccountDaoImpl implements AccountDao {

    //针对用户提供增删改查的方法实现
    private static final String FIND_ACCOUNT_BY_USERNAME_AND_PASSWORD ="SELECT "+
            "LOGIN.ID," +
            "ACCOUNT.EMAIL,ACCOUNT.FIRSTNAME,ACCOUNT.LASTNAME,ACCOUNT.STATUS,"+
            "ACCOUNT.ADDR1 AS address1,ACCOUNT.ADDR2 AS address2,"+
            "ACCOUNT.CITY,ACCOUNT.STATE,ACCOUNT.ZIP,ACCOUNT.COUNTRY,ACCOUNT.PHONE,"+
            "PROFILE.LANGPREF AS languagePreference,PROFILE.FAVCATEGORY AS favouriteCategoryId,"+
            "PROFILE.MYLISTOPT AS listOption,PROFILE.BANNEROPT AS bannerOption,"+
            "BANNERDATA.BANNERNAME "+
            "FROM ACCOUNT, PROFILE,LOGIN,BANNERDATA "+
            "WHERE ACCOUNT.ID=? AND LOGIN.PASSWORD=? "+
            "AND LOGIN.ID = ACCOUNT.ID "+
            "AND PROFILE.ID = ACCOUNT.ID "+
            "AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY ";

    private static final String FIND_ACCOUNT_BY_USERNAME="SELECT * FROM ACCOUNT WHERE LOGIN.USERNAME=?";

    @Override
    public Account findUserByUsernameAndPassword(Account account) {
        Account account2 =null;
        try {
            Connection connection=DBUtil.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement(FIND_ACCOUNT_BY_USERNAME_AND_PASSWORD);
            preparedStatement.setString(1, account.getUsername());
            preparedStatement.setString(2, account.getPassword());
            ResultSet resultSet=preparedStatement.executeQuery();
            if(resultSet.next()){
                account2 =this.resultSetToUser(resultSet);
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return account2;
    }

    @Override
    public boolean findAccountByUsername(String username) {
       boolean result = false;
        try {
            Connection connection=DBUtil.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement(FIND_ACCOUNT_BY_USERNAME);
            preparedStatement.setString(1, username);
            ResultSet resultSet=preparedStatement.executeQuery();
            if(resultSet.next()){
                result = true;
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public boolean insertAccount(Account account) {
        return false;
    }



    private Account resultSetToUser (ResultSet resultSet) throws SQLException {
        Account account =new Account();
        account.setUsername(resultSet.getString("id"));
        account.setEmail(resultSet.getString("email"));
        account.setFirstName(resultSet.getString("firstName"));
        account.setLastName(resultSet.getString("lastName"));
        account.setStatus(resultSet.getString("status"));
        account.setAddress1(resultSet.getString("address1"));
        account.setAddress2(resultSet.getString("address2"));
        account.setCity(resultSet.getString("city"));
        account.setState(resultSet.getString("state"));
        account.setZip(resultSet.getString("zip"));
        account.setCountry(resultSet.getString("country"));
        account.setPhone(resultSet.getString("phone"));
        account.setLanguagePreference(resultSet.getString("languagePreference"));
        account.setFavouriteCategoryId(resultSet.getString("favouriteCategoryId"));
        account.setListOption(resultSet.getString("listOption"));
        account.setBannerOption(resultSet.getString("bannerOption"));
        account.setBannerName(resultSet.getString("bannerName"));
        return account;
    }



//    private static final String INSERT_USER="insert into student values(?,?,?,?,?,?)";
//
//    private static final String SELECT_USER="select * from student where id=?";
//
//    private static final String FIND_USER_BY_USERNAME_AND_PASSWORD="select * from student where username=? and password=?";
//
//    private static final String FIND_ALL_USERS="select * from student";
//
//    public List<User> findAllUsers(){
//        List<User> users=new ArrayList<User>();
//        try{
//            Connection connection= DBUtil.getConnection();
//            Statement statement=connection.createStatement();
//            ResultSet resultSet=statement.executeQuery(FIND_ALL_USERS);
//            while(resultSet.next()){
//                User user=this.resultSetToUser(resultSet);
//                users.add(user);
//            }
//            DBUtil.closeResultSet(resultSet);
//            DBUtil.closeStatement(statement);
//            DBUtil.closeConnection(connection);
//        }catch (Exception e){
//            e.printStackTrace();
//        }
//        return users;
//    }
//
//
//    @Override
//    public User findUserByUsernameAndPassword(String username, String password) {
//        User user=null;
//        try {
//            Connection connection=DBUtil.getConnection();
//            PreparedStatement preparedStatement=connection.prepareStatement(FIND_USER_BY_USERNAME_AND_PASSWORD);
//            preparedStatement.setString(1,username);
//            preparedStatement.setString(2,password);
//            ResultSet resultSet=preparedStatement.executeQuery();
//            if(resultSet.next()){
//                user=this.resultSetToUser(resultSet);
//            }
//            DBUtil.closeResultSet(resultSet);
//            DBUtil.closePreparedStatement(preparedStatement);
//            DBUtil.closeConnection(connection);
//        }catch (Exception e){
//            e.printStackTrace();
//        }
//        return user;
//    }
//
//
//    public User findUser(int id){
//        User user=null;
//        try {
//            Connection connection=DBUtil.getConnection();
//            PreparedStatement preparedStatement=connection.prepareStatement(SELECT_USER);
//            preparedStatement.setInt(1,id);
//            ResultSet resultSet=preparedStatement.executeQuery();
//            if(resultSet.next()){
//               user=this.resultSetToUser(resultSet);
//            }
//            DBUtil.closeResultSet(resultSet);
//            DBUtil.closePreparedStatement(preparedStatement);
//            DBUtil.closeConnection(connection);
//        }catch (Exception e){
//            e.printStackTrace();
//        }
//        return user;
//    }
//
//    public boolean insertUser(User user){
//        boolean flag=false;
//        try {
//            Connection connection=DBUtil.getConnection();
//            PreparedStatement statement=connection.prepareStatement(INSERT_USER);
//            statement.setInt(1,user.getId());
//            statement.setString(2,user.getName());
//            statement.setString(3,user.getPassword());
//            statement.setString(4,user.getEmail());
//            statement.setInt(5,user.getAge());
//            statement.setInt(6,user.isIs_admin()?1:0);
//            int rows=statement.executeUpdate();
//            if(rows>0){
//                flag=true;
//            }
//            DBUtil.closePreparedStatement(statement);
//            DBUtil.closeConnection(connection);
//        }catch (Exception e){
//            e.printStackTrace();
//        }
//        return flag;
//    }
//

}
